package canglang.monitor.core.codelookup.dao;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import canglang.monitor.core.base.dao.BaseDAO;
import canglang.monitor.core.base.properties.AppDefaultProperty;
import canglang.monitor.core.base.util.DateUtil;
import canglang.monitor.core.codelookup.config.CodeLookupBean;
import canglang.monitor.core.codelookup.model.CodeModel;


@SuppressWarnings("unchecked")
public class CodeLookupDAO extends BaseDAO {
	
	private Logger logger = Logger.getLogger(CodeLookupDAO.class);
	

	
	/**
     * Return the <code>CodeTO</code> bean for the code specified by 
     * the <code>CodeLookupBean</code> and the code Id, 
     * regardless if the code is active or inactive.
     * 
	 * @param lookupBean lookupBean <code>LookupBean</code> for code type.
	 * @param codeId code id.
	 * @return <code>CodeTO</code>
	 * @throws Exception
	 */
	public CodeModel getCode(CodeLookupBean lookupBean, String codeId)
	throws Exception {
		// 1. Get the active code and set CodeTO.active = true.
		// 2. If no active code is found then get the non-active one and set CodeTO.active = false.
		// OR
		// 1. Get the code regardless if it is active or inactive.
		// 2. SELECT on active code. If ResultSet.next() is true then set CodeTO.active = true else set it to false.

		CodeModel codeTO = null;
		
		String tableName = lookupBean.getCodeTable();
		String codeIdCol = lookupBean.getCodeIdCol();
		String codeDescCol = lookupBean.getCodeDescCol();
		String codeDescColName = lookupBean.parseCodeDescCol();
		String effStartDtCol = lookupBean.getEffStartDtCol();
		String effEndDtCol = lookupBean.getEffEndDtCol();
		String obsCol = lookupBean.getObsoleteCol();
		List obsoleteValueList = lookupBean.getObsoleteValueList();
		
    	Connection oConnection = null;
    	PreparedStatement selectCodeIdPstmt = null;
    	ResultSet selectCodeIdRS = null;
    	PreparedStatement selectActiveCodeIdPstmt = null;
    	ResultSet selectActiveCodeIdRS = null;
    	
		// 3 possible setting:
		// 1. Both effDate and obsoleteVal are == false.
		//    - all codes in code table are always active
		// 2. effDate == false and obsoleteVal == true.
		//    - only contains obsolete indicator coloumn 
    	//      but no effect date columns.
		// 3. effDate == true and obsoleteVal == false.
		//    - only have start and end effective date columns
    	//      but no obsolete indicator.
		boolean effDate = false;
		boolean effEndDateCol = false;
		boolean obsoleteVal = false;
    	
    	StringBuffer selectCodeIdSQL = new StringBuffer();
    	constructSelectSQL(selectCodeIdSQL, tableName, codeIdCol, codeDescCol);
    	selectCodeIdSQL.append(" WHERE ");
    	selectCodeIdSQL.append(codeIdCol);
    	selectCodeIdSQL.append(" = ? ");

    	logger.debug("CodeLookupDAO.getCode() selectCodeIdSQL: " + selectCodeIdSQL.toString());
    	
    	StringBuffer selectActiveCodeIdSQL =
    		new StringBuffer(selectCodeIdSQL.toString());
    	if (effStartDtCol != null) {
    		effDate = true;
    		selectActiveCodeIdSQL.append(" AND ");
    		effEndDateCol = constructEffectiveDateSQL(selectActiveCodeIdSQL, effStartDtCol, effEndDtCol);
    	} else if (obsCol != null && obsoleteValueList.size() > 0) {
    		obsoleteVal = true;
    		selectActiveCodeIdSQL.append(" AND ");
    		constructObsoleteValSQL(selectActiveCodeIdSQL, obsCol, obsoleteValueList);
    	}
    	logger.debug("CodeLookupDAO.getCode() selectActiveCodeIdSQL: " + selectActiveCodeIdSQL.toString());
    	try {   
    		oConnection = getConnection();		
    		selectCodeIdPstmt =
    			oConnection.prepareStatement(selectCodeIdSQL.toString());
    		selectCodeIdPstmt.setString(1, codeId);
    		selectCodeIdRS = selectCodeIdPstmt.executeQuery();
    		if (selectCodeIdRS.next()) {
    			codeTO = new CodeModel();
    			codeTO.setCodeId(selectCodeIdRS.getString(codeIdCol));
    			codeTO.setCodeDesc(selectCodeIdRS.getString(codeDescColName));
    		}
    		if (codeTO != null) {
    			selectActiveCodeIdPstmt =
    				oConnection.prepareStatement(selectActiveCodeIdSQL.toString());
    			selectActiveCodeIdPstmt.setString(1, codeId);
    			if (effDate) {
        			java.sql.Date startOfDay =
        				new java.sql.Date(DateUtil.getStartOfDay(DateUtil.getDate()).getTime());
        			java.sql.Date endOfDay =
        				new java.sql.Date(DateUtil.getEndOfDay(DateUtil.getDate()).getTime());
        			selectActiveCodeIdPstmt.setDate(2, endOfDay);
        			if (effEndDateCol) {
        				selectActiveCodeIdPstmt.setDate(3, startOfDay);
        			}
    			} else if (obsoleteVal) {
        			for (int i = 0; i < obsoleteValueList.size(); i++) {
        				selectActiveCodeIdPstmt.setString( i + 1 + 1,
        						(String)obsoleteValueList.get(i) );
        			}
    			}
    			selectActiveCodeIdRS = selectActiveCodeIdPstmt.executeQuery();
    			if (selectActiveCodeIdRS.next()) {
    				codeTO.setActive(true);
    			} else {
    				codeTO.setActive(false);
    			}
    		}
    	} catch(Throwable ex) {
    		throw new RuntimeException(ex);
    	} finally {
    		if (selectCodeIdRS != null) {
    			selectCodeIdRS.close();
    		}
    		if (selectActiveCodeIdRS != null) {
    			selectActiveCodeIdRS.close();
    		}
    		if (selectCodeIdPstmt != null) { 
    			selectCodeIdPstmt.close();
    		}
    		if (selectActiveCodeIdPstmt != null) { 
    			selectActiveCodeIdPstmt.close();
    		}
    		if (oConnection != null) {
    			oConnection.close();
    		}
    	}
		return codeTO;
	}
	
	/**
     * Return the List of <code>CodeTO</code> beans for the specified <code>CodeLookupBean</code>
     * regardless if the code is active or inactive.
     * <p>
     * An empty List is returned if there are no entries(records) for the code type.
	 * 
	 * @param lookupBean lookupBean <code>LookupBean</code> for code type.
	 * @return a list of <code>CodeTO</code> regardless if it is active or inactive.
	 * @throws Exception
	 */
	public List getCodeList(CodeLookupBean lookupBean) throws Exception {
		List codeList = new ArrayList();
		String tableName = lookupBean.getCodeTable();
		String codeIdCol = lookupBean.getCodeIdCol();
		String codeDescCol = lookupBean.getCodeDescCol();
		String codeDescColName = lookupBean.parseCodeDescCol();
		
    	Connection oConnection = null;
    	PreparedStatement pstmt = null;
    	ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer();
    	constructSelectSQL(sql, tableName, codeIdCol, codeDescCol, codeDescColName);
    	constructGroupBySQL(sql, codeIdCol);
    	if (!codeIdCol.equals(codeDescColName)) {
    		constructOrderBySQL(sql, 1);
    	}
    	logger.debug("CodeLookupDAO.getCodeList() regardless if the code is active or inactive: " + sql.toString());
    	try {
    		
    		oConnection = getConnection();
    		pstmt = oConnection.prepareStatement(sql.toString());
    		rs = pstmt.executeQuery();
    		while (rs.next()) {
    			CodeModel codeTO = new CodeModel(rs.getString(codeIdCol),
    					rs.getString(codeDescColName), true);
    			codeList.add(codeTO);
    		}
    	} catch(Throwable ex) {
    		throw new RuntimeException(ex);
    	} finally {
    		if (rs != null) {
    			rs.close();
    		}
    		if (pstmt != null) { 
    			pstmt.close();
    		}
    		if (oConnection != null) {
    			oConnection.close();
    		}
    	}    	
		return codeList;
	}
	
	/**
     * Return the List of active <code>CodeTO</code> beans.
     * 
     * <p>
     * An empty List is returned if there are no entries(records) for the code type.
     * 
	 * @param lookupBean <code>LookupBean</code> for code type.
	 * @param includeCode the list must include this <code>CodeTO</code>
	 *        regardless if it is active or inactive.  This can be <code>null<code>.
	 * @param filterName the filter name from config xml.  This can be <code>null<code>.
	 * @param filterParamList Can't be <code>null<code>.
	 * @return a list of active <code>CodeTO</code>.
	 * @throws Exception
	 */
	public List getCodeList(CodeLookupBean lookupBean, String includeCode,
			String filterName, List filterParamList) throws Exception {
		List codeList = new ArrayList();
		String tableName = lookupBean.getCodeTable();
		String codeIdCol = lookupBean.getCodeIdCol();
		String codeDescCol = lookupBean.getCodeDescCol();
		String codeDescColName = lookupBean.parseCodeDescCol();
		
		// Effective Date
		String effStartDtCol = lookupBean.getEffStartDtCol();
		String effEndDtCol = lookupBean.getEffEndDtCol();
		
		// Obsolete indicator
		String obsCol = lookupBean.getObsoleteCol();
		List obsoleteValueList = lookupBean.getObsoleteValueList();
		
		String filter = null;
		if (filterName != null && !filterName.equals("")) {
			filter = lookupBean.getSqlFilter(filterName);
		}
		
		// 3 possible setting:
		// 1. Both effDate and obsoleteVal are == false.
		//    - all codes in code table are always active
		// 2. effDate == false and obsoleteVal == true.
		//    - only contains obsolete indicator coloumn
		//      but no effect date columns.
		// 3. effDate == true and obsoleteVal == false.
		//    - only have start and end effective date columns
		//      but no obsolete indicator.
		boolean effDate = false;
		boolean effEndDateCol = false;
		boolean obsoleteVal = false;
		
    	Connection oConnection = null;
    	PreparedStatement pstmt = null;
    	ResultSet rs = null;
    	
    	StringBuffer sql = new StringBuffer();
    	constructSelectSQL(sql, tableName, codeIdCol, codeDescCol, codeDescColName);
    	if (effStartDtCol != null) {
    		effDate = true;
        	sql.append(" WHERE ");
        	effEndDateCol = constructEffectiveDateSQL(sql, effStartDtCol, effEndDtCol);
    	} else if (obsCol != null && obsoleteValueList.size() > 0) {
    		obsoleteVal = true;
        	sql.append(" WHERE ");
    		constructObsoleteValSQL(sql, obsCol, obsoleteValueList);
    	}
    	
    	if (filter != null) {
    		if (effDate || obsoleteVal) {
    			sql.append(" AND ");
    		} else {
    			sql.append(" WHERE ");
    		}
    		constructFilterSQL(sql, filter);
    	}
    	
    	if (includeCode != null) {
    		if (effDate || obsoleteVal || filter != null) {
    			sql.append(" OR ");
    		} else {
    			sql.append(" WHERE ");
    		}
    		constructIncludeCodeSQL(sql, codeIdCol, includeCode);
    	}
    	constructGroupBySQL(sql, codeIdCol);
    	if (!codeIdCol.equals(codeDescColName)) {
    		constructOrderBySQL(sql, 1);
    	}
    	
    	    	
    	logger.debug("CodeLookupDAO.getCodeList() with includeCode: " + sql.toString());
    	try {
    		
    		oConnection =getConnection();
    		pstmt = oConnection.prepareStatement(sql.toString());
    		int currentParamIndex = 1;
    		if (effDate) {
    			java.sql.Date startOfDay =
    				new java.sql.Date(DateUtil.getStartOfDay(DateUtil.getDate()).getTime());
    			java.sql.Date endOfDay =
    				new java.sql.Date(DateUtil.getEndOfDay(DateUtil.getDate()).getTime());
    			pstmt.setDate(1, endOfDay);
    			if (effEndDateCol) {
    				pstmt.setDate(2, startOfDay);
    				currentParamIndex = 2 + 1;
    			} else {
    				currentParamIndex = 1 + 1;
    			}
    		} else if (obsoleteVal) {
    			for (int i = 0; i < obsoleteValueList.size(); i++) {
    				pstmt.setString( currentParamIndex, (String)obsoleteValueList.get(i) );
    				currentParamIndex++;
    			}
    		}
    		if (filter != null) {
    			for (int i = 0; i < filterParamList.size(); i++) {
    				pstmt.setObject(currentParamIndex, filterParamList.get(i) );
    				currentParamIndex++;
    			}
    		}
    		rs = pstmt.executeQuery();
    		while (rs.next()) {
    			CodeModel codeTO = new CodeModel(rs.getString(codeIdCol),
    					rs.getString(codeDescColName), true);
    			codeList.add(codeTO);
    		}
    	} catch(Throwable ex) {
    		throw new RuntimeException(ex);
    	} finally {
    		if (rs != null) {
    			rs.close();
    		}
    		if (pstmt != null) { 
    			pstmt.close();
    		}
    		if (oConnection != null) {
    			oConnection.close();
    		}
    	}    	
		return codeList;
	}
	
	/**
	 * Construct base select statment with given table name, code id column,
	 * code desc string and code desc column name.
	 * 
	 * @param buffer can't be null.  The SQL statement will be appended to this
	 *               StringBuffer.
	 * @param tableName table name.
	 * @param codeIdCol code id column name.
	 * @param codeDescCol code desc column string. This could be an sql statement.
	 * @param codeDescName code desc column name.
	 */
	private void constructSelectSQL(StringBuffer buffer, String tableName,
			String codeIdCol, String codeDescCol, String codeDescName) {
		String codeDescSql = getCodeDescSql(codeDescCol);
		buffer.append("SELECT ");
    	buffer.append(codeIdCol);
    	// Later on the "group by" will be appended to this.
    	// So we have added a HACK to use "MAX" in the code desc column.
    	// Then group by code id to select unique code id.
    	buffer.append(", MAX(");
    	buffer.append(codeDescSql);
    	buffer.append(") as ");
    	buffer.append(codeDescName);
    	buffer.append(" FROM ");
    	buffer.append(tableName);
    	buffer.append(" ");
	}
	
	/**
	 * Construct base select statment with given table name, code id and
	 * code desc columns.
	 * 
	 * @param buffer can't be null.  The SQL statement will be appended to this
	 *               StringBuffer.
	 * @param tableName table name.
	 * @param codeIdCol code id column name.
	 * @param codeDescCol code desc column name.
	 */	
	private void constructSelectSQL(StringBuffer buffer, String tableName,
			String codeIdCol, String codeDescCol) {
    	buffer.append("SELECT DISTINCT ");
    	buffer.append(codeIdCol);
    	buffer.append(", ");
    	buffer.append(codeDescCol);
    	buffer.append(" FROM ");
    	buffer.append(tableName);
    	buffer.append(" ");

	}
	
	/**
	 * Construct SQL statement for given effective start and end date columns.
	 * 
	 * @param buffer can't be null.  The SQL statement will be appended to this
	 *               StringBuffer.
	 * @param effStartDtCol effective start date column.
	 * @param effEndDtCol effective end date column.
	 */
	private boolean constructEffectiveDateSQL(StringBuffer buffer,
			String effStartDtCol, String effEndDtCol) {
		boolean hasEffectEndDtCol = true;
		buffer.append(" ");
		buffer.append(effStartDtCol);
		if (effEndDtCol != null && !effEndDtCol.equals("")) {
			hasEffectEndDtCol = true;
			buffer.append(" <= ? AND ( ");
			buffer.append(effEndDtCol);
			buffer.append(" >= ? OR ");
			buffer.append(effEndDtCol);
			buffer.append(" is null )");
		} else {
			hasEffectEndDtCol = false;
			buffer.append(" <= ? ");
		}
		return hasEffectEndDtCol;
	}
	
	/**
	 * Construct obsolete SQL statement for indicator column.
	 * 
	 * @param buffer can't be null.  The SQL statement will be appended to this
	 *               StringBuffer.
	 * @param obsCol obsolete indicator column.
	 * @param obsoleteValueList list of obsolete values.
	 */
	private void constructObsoleteValSQL(StringBuffer buffer, String obsCol,
			List obsoleteValueList) {
		buffer.append(" ( ");
		if (obsoleteValueList.size() > 0) {
			buffer.append(obsCol);
			buffer.append(" != ? ");
		}
		for (int i = 1; i < obsoleteValueList.size(); i++) {
			buffer.append(" AND ");
			buffer.append(obsCol);
			buffer.append(" != ? ");
		}
		buffer.append(" ) ");
	}
	
	/**
	 * Construct SQL statement for including the given code id.
	 * 
	 * @param buffer can't be null.  The SQL statement will be appended to this
	 *               StringBuffer.
	 * @param codeIdCol code id column.
	 * @param includeCode the included code id.
	 */
	private void constructIncludeCodeSQL(StringBuffer buffer, String codeIdCol,
			String includeCode) {
		buffer.append(codeIdCol);
		buffer.append(" = '");
		buffer.append(includeCode);
		buffer.append("' ");
	}
	
	/**
	 * Construct SQL statement for filter.
	 * 
	 * @param buffer can't be null.  The SQL statement will be appended to this
	 *               StringBuffer.
	 * @param filter the SQL statement for this filter.
	 */
	private void constructFilterSQL(StringBuffer buffer, String filter) {
		buffer.append(" ( ");
		buffer.append(filter);
		buffer.append(" ) ");
	}
	
	/**
	 * Group by code id to make sure only one code id is returned.
	 * 
	 * @param buffer can't be null.  The SQL statement will be appended to this
	 *               StringBuffer.
	 * @param codeIdCol code id column name.
	 */
	private void constructGroupBySQL(StringBuffer buffer, String codeIdCol) {
		//group by codeIdCol
		buffer.append(" GROUP BY ");
		buffer.append(codeIdCol);
		buffer.append(" ");
	}
	
	/**
	 * Construct SQL statement to order by code desc column.
	 * 
	 * @param buffer can't be null.  The SQL statement will be appended to this
	 *               StringBuffer.
	 * @param codeDescCol code desc column.
	 */
	private void constructOrderBySQL(StringBuffer buffer, int orderByCol) {
		buffer.append(" ORDER BY ");
		buffer.append(orderByCol);
		buffer.append(" ");
	}
	
	/**
	 * Get the code desc col from xml and strip out "as codeDescName" part.
	 * 
	 * @param codeDescCol
	 * @return
	 */
	private String getCodeDescSql(String codeDescCol) {
		// HACK!!!	
		String sql = null;
		if (codeDescCol != null && !codeDescCol.equals("")) {
			String[] result = codeDescCol.trim().split("\\s");
			if (result.length == 1) {
				sql = result[result.length-1];
			} else if (result.length > 1 && result[result.length - 2].equalsIgnoreCase("as")) {
				StringBuffer buf = new StringBuffer();
				for (int i =0; i < result.length -2; i++) {
					buf.append(result[i]);
					buf.append(" ");
				}
				sql = buf.toString();
			}
		}
		return sql;
	}
	
	private Connection getConnection(){
		try {
			Class.forName(AppDefaultProperty.getProperty("jdbc.driverClassName"));    
			String url = AppDefaultProperty.getProperty("jdbc.url");
			String user = AppDefaultProperty.getProperty("jdbc.username");    
			String psw = AppDefaultProperty.getProperty("jdbc.password");    
			return DriverManager.getConnection(url,user,psw); 
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
